from backend.database.base_db import BaseDB
from backend.database.base_db import BaseDB
from backend.models.order_detail import OrderDetail

class OrderDetailDB(BaseDB):
    def __init__(self):
        super().__init__('order_detail')
    
    def get_detail_by_id(self, order_detail_id):
        query = "SELECT * FROM order_detail WHERE order_detail_id = %s"
        result = self.fetch_one(query, (order_detail_id,))
        return OrderDetail.from_dict(result) if result else None
    
    def get_details_by_order_id(self, order_id):
        query = "SELECT * FROM order_detail WHERE order_id = %s"
        results = self.fetch_all(query, (order_id,))
        return [OrderDetail.from_dict(result) for result in results]
    
    def get_details_by_food_id(self, food_id):
        query = "SELECT * FROM order_detail WHERE food_id = %s"
        results = self.fetch_all(query, (food_id,))
        return [OrderDetail.from_dict(result) for result in results]
    
    def add_order_detail(self, order_detail):
        data = order_detail.to_dict()
        # 移除可能存在的None值
        data = {k: v for k, v in data.items() if v is not None}
        # 如果有order_detail_id，也移除，让数据库自动生成
        if 'order_detail_id' in data:
            del data['order_detail_id']
        
        return self.insert(data)
    
    def add_order_details(self, order_details):
        if not order_details:
            return 0
        
        data_list = []
        for detail in order_details:
            data = detail.to_dict()
            # 移除可能存在的None值
            data = {k: v for k, v in data.items() if v is not None}
            # 如果有order_detail_id，也移除，让数据库自动生成
            if 'order_detail_id' in data:
                del data['order_detail_id']
            data_list.append(data)
        
        return self.insert_many(data_list)
    
    def update_order_detail(self, order_detail):
        data = order_detail.to_dict()
        order_detail_id = data.pop('order_detail_id', None)
        if order_detail_id is None:
            raise ValueError("订单详情ID不能为空")
        
        # 移除可能存在的None值
        data = {k: v for k, v in data.items() if v is not None}
        
        return self.update(data, f"order_detail_id = {order_detail_id}")
    
    def delete_order_detail(self, order_detail_id):
        return self.delete(f"order_detail_id = {order_detail_id}")
    
    def delete_order_details_by_order_id(self, order_id):
        return self.delete(f"order_id = {order_id}")
    
    def get_total_amount_by_order_id(self, order_id):
        query = "SELECT SUM(subtotal) as total FROM order_detail WHERE order_id = %s"
        result = self.fetch_one(query, (order_id,))
        return result['total'] if result and result['total'] is not None else 0